package com.greatwideweb.runhelper.db;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.greatwideweb.runhelper.RunItem;
import com.greatwideweb.runhelper.db.DatabaseHelper;

public class DatabaseAdapter {
  
  private Context context;
  private SQLiteDatabase database;
  private DatabaseHelper dbHelper;
  
  public DatabaseAdapter(Context context) {
    this.context = context;
  }
  
  public DatabaseAdapter open() throws SQLException {
    dbHelper = new DatabaseHelper(context);
    database = dbHelper.getWritableDatabase();
    return this;
  }
  
  public void close() {
    dbHelper.close();
  }
  
  // @create
  public void saveRun(RunItem runItem) {
    if(runItem != null) {
      deleteRun(runItem);
      createRunItem(runItem); 
    }
  }
  
  // @read get run
  public RunItem fetchRun(GregorianCalendar cal) 
  {
    final String sql="select distance, distance_part, run_time_min, run_time_sec "+
              " from run_data "+
              " where  run_date_yyyy = ? and run_date_mm =? and run_date_dd =?";
    RunItem result=null;
    String[] ar =
    {String.valueOf(cal.get(Calendar.YEAR)),
     String.valueOf(cal.get(Calendar.MONTH)),
     String.valueOf(cal.get(Calendar.DATE))};
    Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
      result = new RunItem(cal,c.getInt(0), c.getInt(1));
      break;
    }
    c.close();
    return result;
    
    
  }
  
  // @delete
  public void deleteRun(RunItem runItem) 
  {
    if(runItem != null) {
      final String sql="delete from run_data where  run_date_yyyy = ? and run_date_mm =? and run_date_dd =?";
      try
      {
        database.beginTransaction();
        database.setTransactionSuccessful();
        SQLiteStatement statement = database.compileStatement(sql);
        statement.bindLong(1, runItem.getRunDate().get(Calendar.YEAR));
        statement.bindLong(2, runItem.getRunDate().get(Calendar.MONTH));
        statement.bindLong(3, runItem.getRunDate().get(Calendar.DATE));
        statement.executeUpdateDelete();
        
      }finally 
      {
        database.endTransaction();
      }
    }
  }
  
  // @insert
  public void createRunItem(RunItem runItem) 
  {
    if(runItem != null) 
    {
      GregorianCalendar now = new GregorianCalendar();
      final String sql=" insert into run_data "+
          " ( "+
          " distance, "+ 
          " distance_part, "+ 
          " run_time_min,  "+
          " run_time_sec, "+ 
          " run_date_yyyy,  "+
          " run_date_mm,  "+
          " run_date_dd,  "+
          " updated_date_yyyy,  "+
          " updated_date_mm,  "+
          " updated_date_dd "+
          " ) "+
          " values "+
          " ( "+
          " ?,  "+
          " ?,  "+
          " ?,  "+
          " ?,  "+
          " ?,  "+
          " ?,  "+
          " ?,  "+
          " ?, "+
          " ?, "+
          " ? "+
          " ) ";
      try
      {
        database.beginTransaction();
        database.setTransactionSuccessful();
        SQLiteStatement statement = database.compileStatement(sql);
        statement.bindLong(1, runItem.getDistance());
        statement.bindLong(2, runItem.getDistancePart());
        statement.bindLong(3, runItem.getRunTimeMinutes());
        statement.bindLong(4, runItem.getRunTimeSeconds());
        statement.bindLong(5, runItem.getRunDate().get(Calendar.YEAR));
        statement.bindLong(6, runItem.getRunDate().get(Calendar.MONTH));
        statement.bindLong(7, runItem.getRunDate().get(Calendar.DATE));
        statement.bindLong(8, now.get(Calendar.YEAR));
        statement.bindLong(9, now.get(Calendar.MONTH));
        statement.bindLong(10, now.get(Calendar.DATE));
        statement.executeUpdateDelete();
        
      }finally 
      {
        database.endTransaction();
      }
    }
  }

  public ArrayList<RunItem> fetchAll() 
  {
    //Log.d("fetchAll", "start");
    final String sql="select distance, distance_part, run_time_min, run_time_sec, "+
        "run_date_dd, run_date_mm,run_date_yyyy from run_data ";
      ArrayList<RunItem>  result = new ArrayList<RunItem>();
      Cursor c = database.rawQuery(sql, null);
      c.moveToFirst();
      while (c.isAfterLast() == false) {
        GregorianCalendar cal = new GregorianCalendar(c.getInt(6), c.getInt(5), c.getInt(4));
        RunItem item = new RunItem(cal,c.getInt(0), c.getInt(1));
        result.add(item);
        c.moveToNext();
      }
      c.close();
      return result;
  }
  
  

}
